﻿using sqlsearcher.Models;
using sqlsearcher.Models.Services;
using sqlsearcher.Services.IService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sqlsearcher.Services.DB
{
    /// <summary>
    /// SqlServerSearch
    /// 创建日期 2023/1/17 16:45:34
    /// Author: 名字什么都是浮云
    /// </summary>	
    public class SqlServerSearch : ISearch
    {
        public Tuple<bool, List<TreeMapInfo>> GetDataBases(ISqlDbUtils dbUtils)
        {
            string sql = "select name as Node,'' ParentNode,'0' IsKey from sysdatabases order by name asc";

            DataTable dt = new SqlServerDbUtils().Query(sql);

            var maps = dt.ToList<TreeMapInfo>();

            return Tuple.Create(maps != null && maps.Count > 0, maps);
        }

        public Tuple<bool, List<TreeMapInfo>> GetTables(ISqlDbUtils dbUtils, string db)
        {
            string sql = "select name Node,'{0}' ParentNode,'0' IsKey from [{0}].dbo.sysobjects WHERE xtype='U' order by name asc";

            DataTable dt = dbUtils.Query(db,string.Format(sql, db));

            var maps = dt.ToList<TreeMapInfo>();

            return Tuple.Create(maps != null && maps.Count > 0, maps);
        }

        public Tuple<bool, List<TreeMapInfo>> GetColumns(ISqlDbUtils dbUtils, string db, string value)
        {
            string sql = @"use [$db]
            SELECT
            a.name Node,
            '$value' ParentNode,
            case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                    SELECT name FROM sysindexes WHERE indid in(
                    SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
                    ))) then '1' else '0' end as IsKey
            FROM syscolumns a
            inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'dtproperties'
            where d.name = '$value'
            order by a.id,a.colorder";
            sql = sql.Format(new { db = db, value = value });
            DataTable dt = dbUtils.Query(db, sql);

            var maps = dt.ToList<TreeMapInfo>();

            return Tuple.Create(maps != null && maps.Count > 0, maps);
        }

        public Tuple<int, DataTable> GetDetails(ISqlDbUtils dbUtils, string db, string value)
        {
            string sql = @"
            use [$db]
            SELECT
                                cName=a.name,
                                cDesc=isnull(g.[value],''),
                                cTag=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '自增'else '' end,
                                cKey=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                                SELECT name FROM sysindexes WHERE indid in(
                                SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
                                ))) then '主键' else '' end,
                                cType=b.name+'('+convert(nvarchar(50),COLUMNPROPERTY(a.id,a.name,'PRECISION'))+')',
                                cNul=case when a.isnullable=1 then '可空'else '' end,
                                cDef=isnull(e.text,'')
                                FROM syscolumns a
                                left join systypes b on a.xusertype=b.xusertype
                                inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
                                left join syscomments e on a.cdefault=e.id
                                left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
                                left join sys.extended_properties f on d.id=f.major_id and f.major_id=0
                                where d.name='$value'   
                                order by a.id,a.colorder
              ";
            sql = sql.Format(new { db = db, value = value });
            var dt = dbUtils.Query(db,sql);
            var isHas = dt != null && dt.Rows.Count > 0;
            return Tuple.Create(isHas ? dt.Rows.Count : 0, dt);
        }

        public Tuple<int, DataTable> GetCodeDetails(ISqlDbUtils dbUtils, string db, string value)
        {
            string sql = @"
            use [$db]
            SELECT
                                cName=a.name,
                                cDesc=isnull(g.[value],''),
                                cTag=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '自增'else '' end,
                                cKey=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                                SELECT name FROM sysindexes WHERE indid in(
                                SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
                                ))) then '主键' else '' end,
                                cType=b.name,
                                cNul=case when a.isnullable=1 then '可空'else '' end,
                                cDef=isnull(e.text,'')
                                FROM syscolumns a
                                left join systypes b on a.xusertype=b.xusertype
                                inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
                                left join syscomments e on a.cdefault=e.id
                                left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
                                left join sys.extended_properties f on d.id=f.major_id and f.major_id=0
                                where d.name='$value'   
                                order by a.id,a.colorder
              ";
            sql = sql.Format(new { db = db, value = value });
            var dt = dbUtils.Query(db, sql);
            var isHas = dt != null && dt.Rows.Count > 0;
            return Tuple.Create(isHas ? dt.Rows.Count : 0, dt);
        }

        public Tuple<int, DataTable> GetDescs(ISqlDbUtils dbUtils, string db, string tb)
        {
            string sql = @"
            use [$db]
            SELECT
                                a.name as 'DcName',
                                isnull(g.[value],'') as 'Desc',
                                isnull(g.[value],'') as 'Desc2'
                                FROM syscolumns a
                                left join systypes b on a.xusertype=b.xusertype
                                inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
                                left join syscomments e on a.cdefault=e.id
                                left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
                                left join sys.extended_properties f on d.id=f.major_id and f.major_id=0
                                where d.name='$value'   
                                order by a.id,a.colorder
              ";
            sql = sql.Format(new { db = db, value = tb });
            var dt = dbUtils.Query(db,sql);
            var isHas = dt != null && dt.Rows.Count > 0;
            return Tuple.Create(isHas ? dt.Rows.Count : 0, dt);
        }

        public string GetTableDesc(ISqlDbUtils dbUtils, string tbName, string dbName)
        {
            string sql = @"SELECT tbs.name,ds.value
            FROM sys.extended_properties ds  
            LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id  
            WHERE  ds.minor_id=0 and  
             tbs.name='$tableName'";

            DataTable dt = dbUtils.Query(dbName,sql.Format(new { tableName = tbName }));
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0]["value"].TryString();
            }
            return tbName;
        }

        public List<string> GetTableNamesByDB(ISqlDbUtils dbUtils, string db)
        {
            var res = new List<string>();
            string sql = "select name Node,'{0}' ParentNode,0 IsKey from [{0}].dbo.sysobjects WHERE xtype='U' order by name asc";

            DataTable dt = dbUtils.Query(db,string.Format(sql, db));
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    res.Add(item["Node"].TryString());
                }
            }
            return res;
        }

        internal List<string> GetFiledsByTable(ISqlDbUtils dbUtils, string dbName, string tbName)
        {
            //;
            var res = new List<string>();
            string sql = "use {0} \r\n select name from syscolumns where id=Object_Id('{1}') order by name asc";

            DataTable dt = dbUtils.Query(dbName,string.Format(sql, dbName, tbName));
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    res.Add(item["name"].TryString());
                }
            }
            return res;
        }

        internal DataTable GetSelectQuery(ISqlDbUtils dbUtils, string db, string tb, string field, string value, bool isTop, bool isNew, string calc)
        {
            var sql = new StringBuilder(" use [" + db + "] \r\n ");
            sql.AppendFormat("select * from {0} \r\n where 1=1 ", tb);
            if (!string.IsNullOrWhiteSpace(field))
            {

                if (!string.IsNullOrWhiteSpace(value))
                {
                    if (calc == "like")
                    {
                        sql.AppendFormat(" and {0} like'%{1}%'", field, value);
                    }
                    else
                    {
                        sql.AppendFormat(" and {0}{1}'{2}'", field, string.IsNullOrWhiteSpace(calc) ? "=" : calc, value);
                    }
                }
            }
            //  查表数据无条件
            if (isTop)
            {
                //  查表10行
                sql.Insert(sql.ToString().IndexOf("select") + 7, " top 1000 ");
            }
            if (isNew)
            {
                //  查表最新10行
                if (sql.ToString().IndexOf("top") == -1)
                    sql.Insert(sql.ToString().IndexOf("select") + 7, " top 1000 ");

                var tbs = GetCodeDetails(dbUtils,db, tb);
                var key = "id";
                foreach (DataRow item in tbs.Item2.Rows)
                {
                    if (item["cKey"].TryString() == "主键")
                    {
                        key = item["cName"].TryString();
                        break;
                    }
                }
                sql.AppendFormat(" order by {0} desc", key);
            }


            return dbUtils.Query(db,sql.ToString());
        }

        public DataTable GetTablesByDB(ISqlDbUtils dbUtils, string db)
        {
            var sql = $@"use {db};
            select '表名'=tbs.name,'创建日期'=tbs.crdate,'描述'=ds.value
            from sysobjects  tbs
            LEFT JOIN sys.extended_properties ds ON ds.major_id=tbs.id  and ds.minor_id=0
            where tbs.xtype='U'
            order by tbs.crdate desc";
            return dbUtils.Query(db,sql);
        }

        public string GetTableDescSQL()
        {
            var sql = $@"
            SELECT
                TbName = OBJECT_NAME(c.object_id),
	            TbDesc = CONVERT(NVARCHAR(100), isnull(g.[value], '-')),
                CName = c.name,
                CDesc = ex.value
            FROM sys.columns c
            left join sys.extended_properties g on(c.object_id = g.major_id AND g.minor_id = 0)
            LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description'
            WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')= 0
            ORDER BY OBJECT_NAME(c.object_id), c.column_id";
            return sql;
        }

        public DataTable GetDs(ISqlDbUtils dbUtils,string sql, string db)
        {
            return dbUtils.Query(db,sql);
        }

        public DataSet GetDataSets(ISqlDbUtils dbUtils, string sql, string db)
        {
            return dbUtils.QueryDs(db,sql);
        }

        public int Cmd(ISqlDbUtils dbUtils, string sql, string db)
        {
            return dbUtils.Cmd(db, sql);
        }
    }

}
